package cn.huas.action;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.huas.sqls.Command;
import cn.huas.sqls.Recover;
import cn.huas.util.ActionContextUtil;
import cn.huas.util.DBUtil;

import com.opensymphony.xwork2.ActionSupport;

public class MysqlAction extends ActionSupport {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7916468986218022890L;

	public String getFilepath() {
		return filepath;
	}

	public void setFilepath(String filepath) {
		this.filepath = filepath;
	}

	private String filepath;
	private String ip;

	public String getIp() {
		return ip;
	}

	public void setIp(String ip) {
		this.ip = ip;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDatabaseName() {
		return databaseName;
	}

	public void setDatabaseName(String databaseName) {
		this.databaseName = databaseName;
	}

	private String username;
	private String password;
	private String databaseName;

	public void MysqlRec() {
		@SuppressWarnings("unused")
		HttpServletRequest request = ActionContextUtil.getHttpServletRequest();
		HttpServletResponse response = ActionContextUtil
				.getHttpServletResponse();
		response.setCharacterEncoding("utf-8");            
		Recover rec = new Recover();
		boolean flag = rec.load(databaseName, filepath);
		if (flag) {
			try {
				response.getWriter().print("{success:true,msg:'恭喜您，恢复成功'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			try {
				response.getWriter().print(
						"{success:false,msg:'恢复失败，服务器错误，请联系管理员'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public void MysqlBak() {
		@SuppressWarnings("unused")
		HttpServletRequest request = ActionContextUtil.getHttpServletRequest();
		HttpServletResponse response = ActionContextUtil
				.getHttpServletResponse();
		response.setCharacterEncoding("utf-8");
		Command com = new Command();
		boolean flag = com.checkConmmand(ip, username, password, databaseName,
				filepath);
		if (flag) {
			try {
				response.getWriter().print("{success:true,msg:'恭喜您，备份成功'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			try {
				response.getWriter().print(
						"{success:false,msg:'备份失败，服务器错误，请联系管理员'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	//1.初始化数据库中  把t_class_schedule,t_teacher_scheudle,t_classroom-schedule中的数据从计划表取出相应的班级号、教师号、教室号，并把J0....值赋值为0
	//2.把t_teachers表中的c_paike_count，t_classroom中c_paike_count变为0
	//3.清楚计划表中内容
	public void MysqlInit() {
		@SuppressWarnings("unused")
		HttpServletRequest request = ActionContextUtil.getHttpServletRequest();
		HttpServletResponse response = ActionContextUtil
				.getHttpServletResponse();
		response.setCharacterEncoding("utf-8");

		int n = 0;
		Connection conn = null;
		List<PreparedStatement> list = new ArrayList<PreparedStatement>();
        
		//清楚计划表中内容
		//String sql12 = "delete from t_schedule_plan";
		//删除课表中以前的记录
		String sql1 = "delete from t_class_schedule";
		String sql2 = "delete from t_teacher_schedule";
		String sql3 = "delete from t_classroom_schedule";
        //把课表插入计划表表中存在的班级、老师、教室
		String sql4 = "insert into t_class_schedule(c_class_id) select distinct c_class_id from t_schedule_plan";
		String sql5 = "insert into t_teacher_schedule(c_teacher_id) select distinct c_teachers_id from t_schedule_plan";
		String sql6 = "insert into t_classroom_schedule(c_classroom_id) select distinct c_classroom_id from t_schedule_plan";
        //初始化课表中各个排课位置为0
		String sql7 = "update t_class_schedule set j01=0,j02=0,j03=0,j04=0,j05=0,j06=0,j07=0,j08=0,j09=0,j10=0,j11=0,j12=0,j13=0,j14=0,j15=0,j16=0,j17=0,j18=0,j19=0,j20=0 ";
		String sql8 = "update t_teacher_schedule set j01=0,j02=0,j03=0,j04=0,j05=0,j06=0,j07=0,j08=0,j09=0,j10=0,j11=0,j12=0,j13=0,j14=0,j15=0,j16=0,j17=0,j18=0,j19=0,j20=0 ";
		String sql9 = "update t_classroom_schedule set j01=0,j02=0,j03=0,j04=0,j05=0,j06=0,j07=0,j08=0,j09=0,j10=0,j11=0,j12=0,j13=0,j14=0,j15=0,j16=0,j17=0,j18=0,j19=0,j20=0 ";
		//初始化把t_teachers表中的c_paike_count，t_classroom中c_paike_count变为0
		String sql10 = "update t_teachers set c_paike_count=0";
		String sql11 = "update t_classroom set c_paike_count=0";
		
		

		String[] ss = { sql1, sql2, sql3, sql4, sql5, sql6, sql7, sql8, sql9,sql10,sql11};

		conn = DBUtil.createConn();

		for (int i = 0; i < 11; i++) {
			System.out.println(ss[i]);
			list.add(i, DBUtil.prepare(conn, ss[i]));
			System.out.println(list.size());
		}
		try {
			for (int j = 0; j < list.size(); j++) {
				n += list.get(j).executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			for (int k = 0; k < list.size(); k++) {
				try {
					list.get(k).close();
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

		}
		if (n > 1) {
			try {
				response.getWriter().print("{success:true,msg:'恭喜您111，初始化成功'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			try {
				response.getWriter().print(
						"{success:false,msg:'初始化失败111，服务器错误，请联系管理员'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public PreparedStatement createPrepared(int i, Connection conn, String sql) {
		PreparedStatement psi = null;
		try {
			conn.prepareStatement(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return psi;
	}
	
	//最终方案的备份，当排课最终完成之后，把三类课表中数据保存下来
	public void MysqlPKBak(){

		@SuppressWarnings("unused")
		HttpServletRequest request = ActionContextUtil.getHttpServletRequest();
		HttpServletResponse response = ActionContextUtil
				.getHttpServletResponse();
		response.setCharacterEncoding("utf-8");

		int n = 0;
		Connection conn = null;
		List<PreparedStatement> list = new ArrayList<PreparedStatement>();
        
		String sql1 = "insert into t_class_scheduleb(c_class_id,j01,j02,j03,j04,j05,j06,j07,j08,j09,j10,j11,j12,j13,j14,j15,j16,j17,j18,j19,j20) select * from t_classroom_schedule";
		String sql2 = "insert into t_teacher_scheduleb(c_teacher_id,j01,j02,j03,j04,j05,j06,j07,j08,j09,j10,j11,j12,j13,j14,j15,j16,j17,j18,j19,j20) select * from t_classroom_schedule";
		String sql3 = "insert into t_classroom_scheduleb(c_classroom_id,j01,j02,j03,j04,j05,j06,j07,j08,j09,j10,j11,j12,j13,j14,j15,j16,j17,j18,j19,j20) select * from t_classroom_schedule";
		
		String sql4 = "update t_class_scheduleb set  pkdate=curdate() ";
		String sql5 = "update t_teacher_scheduleb set  pkdate=curdate() ";
		String sql6 = "update t_classroom_scheduleb set  pkdate=curdate() ";
		

		String[] ss = { sql1, sql2, sql3, sql4, sql5, sql6};

		conn = DBUtil.createConn();

		for (int i = 0; i < 6; i++) {
			System.out.println(ss[i]);
			list.add(i, DBUtil.prepare(conn, ss[i]));
			System.out.println(list.size());
		}
		try {
			for (int j = 0; j < list.size(); j++) {
				n += list.get(j).executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			for (int k = 0; k < list.size(); k++) {
				try {
					list.get(k).close();
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

		}
		if (n > 1) {
			try {
				response.getWriter().print("{success:true,msg:'恭喜您111，初始化成功'}");
			} catch (IOException e) {
				e.printStackTrace();  
			}
		} else {
			try {
				response.getWriter().print(
						"{success:false,msg:'初始化失败111，服务器错误，请联系管理员'}");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	
	}
}
